package com.hcb.tonguetiphealth.manager;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
  /**
   * @author 008
   * @since 2012-12-27
   */
public final class DBManager {
	private static final String TAG = "DBManager";
	private static final String DATABASE_NAME = "TongueTipHealth.db";
	private static final int DATABASE_VERSION = 1;
	private static DBManager dbManager = null;
	private SQLiteDatabase mDb;
	public static final String table_user = "user";
	public static final String table_wineBrand = "winebrand";
	public static final String table_wineType = "winetype";
	public static final String table_winePrice = "wineprice";
	public static final String table_foodtype = "foodtype";
	public static final String table_plan = "plan";
	private static final String TABLE_USER = "create table user (_id integer primary key autoincrement, "
			+ "username text not null, password text not null, logintime long, rememb integer, autologin integer);";
	private static final String TABLE_WINEBRAND = "create table winebrand (_id integer primary key autoincrement, "
			+ "strid text not null, name text not null);";
	private static final String TABLE_WINETYPE = "create table winetype (_id integer primary key autoincrement, "
			+ "strid text not null, name text not null);";
	private static final String TABLE_WINEPRICE = "create table wineprice (_id integer primary key autoincrement, "
			+ "strid text not null, name text not null);";
	//美食类别
	private static final String TABLE_FOODTYPE = "create table foodtype (_id integer primary key autoincrement, "
		+ "foodtype text not null, childfoodtype text not null);";
	private static final String TABLE_PLAN = "create table plan (_id integer primary key autoincrement, "
		+ "usrid text not null, plan text not null, plandate text not null, plantime text);";
	class DbHelper extends SQLiteOpenHelper {

		public DbHelper(Activity context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(TABLE_USER);
			db.execSQL(TABLE_WINEBRAND);
			db.execSQL(TABLE_WINETYPE);
			db.execSQL(TABLE_WINEPRICE);
			db.execSQL(TABLE_FOODTYPE);
			db.execSQL(TABLE_PLAN);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS " + table_user);
			db.execSQL("DROP TABLE IF EXISTS " + table_wineBrand);
			db.execSQL("DROP TABLE IF EXISTS " + table_wineType);
			db.execSQL("DROP TABLE IF EXISTS " + table_winePrice);
			db.execSQL("DROP TABLE IF EXISTS " + table_foodtype);
			db.execSQL("DROP TABLE IF EXISTS " + table_plan);
			onCreate(db);
		}
		
	}

	private DBManager(final Activity ctx) {
		DbHelper help = new DbHelper(ctx);
		mDb = help.getWritableDatabase();
		
		Log.i(TAG, "------  DBManager  创建数据库");
	}

	public static DBManager getInstance(Activity ctx) {
		if (null == dbManager) {
			dbManager = new DBManager(ctx);
		}
		return dbManager;
	}

	public void release() {
		mDb.close();
		Log.i(TAG, "------  release  关闭数据库");
	}
	/**
	 * 使用后务必关闭Cursor ！！！
	 * <br>
	 * 获取Cursor
	 * </br>
	 * @param distinct
	 * @param table
	 * @param columns
	 * @param where
	 * @param wheres
	 * @param having
	 * @param groupBy
	 * @param orderBy
	 * @param limit
	 * @return
	 */
	public Cursor getCursor(boolean distinct, String table, String[] columns, String where,
			String[] wheres, String having, String groupBy, String orderBy, String limit) {
		Cursor mCursor = null;
		try {
			mCursor = mDb.query(distinct, table, columns, where, wheres,groupBy, having, orderBy, limit);
		} catch(Exception e) {
			Log.e(TAG, "getCursor", e);
		}
		/*String[] columns = {"id", "userName"};
		String where = "id='" + 1 + "'";
		String orderBy = "logintime asc"; //desc
		*/
		return mCursor;
	}
	/**
	 * 使用后务必关闭Cursor ！！！
	 * <br>
	 * 获取Cursor
	 * </br>
	 * @param tableName
	 * @return
	 */
	public Cursor getCursor(String tableName) {
		Cursor cursor = null;
		try {
			cursor = mDb.query(tableName, null, null, null, null, null, null);
		} catch(Exception e) {
			Log.e(TAG, "getCursor", e);
		}
		return cursor;
	}
	
	/**
	 * 使用后务必关闭Cursor ！！！
	 * <br>
	 * 获取Cursor
	 * </br>
	 * @param tableName
	 * @return
	 */
	public Cursor getCursorBySql(String strSql) {
		Cursor cursor = null;
		try {
			cursor = mDb.rawQuery(strSql, null);//不是用占位符 http://dev.10086.cn/cmdn/wiki/index.php?doc-view-6995
		} catch(Exception e) {
			Log.e(TAG, "getCursor", e);
		}
		return cursor;
	}
	
	/**
	 * 插入记录
	 * @param tableName
	 * @param values
	 * @return
	 */
	public long insert(String tableName, ContentValues values) {
		long rowId = -1;
		try {
			rowId = mDb.insert(tableName, null, values);
		} catch(Exception e) {
			Log.e(TAG, "insert", e);
		}
		return rowId;
	}
	/**
	 * 更新记录
	 * @param tableName
	 * @param id
	 * @param values
	 * @return
	 */
	public boolean update(String tableName, String id, ContentValues values) {
		int rows = 0;
		try {
			rows = mDb.update(tableName, values, "_id=?", new String[]{id});
		} catch(Exception e) {
			Log.e(TAG, "update", e);
		}
		return rows > 0;
	}
	public boolean update(String tableName, String key, String value, ContentValues values) {
		int rows = 0;
		try {
			rows = mDb.update(tableName, values, key+"=?", new String[]{value});
		} catch(Exception e) {
			Log.e(TAG, "update", e);
		}
		return rows > 0;
	}
	/**
	 * 使用后务必关闭Cursor ！！！
	 * <br>
	 * 查询表中指定记录
	 * </br>
	 * @param tableName
	 * @param id
	 * @return
	 */
	public Cursor selete(String tableName, int id) {
		Cursor cursor = null;
		if (tableName == null || tableName.length() <= 0) {
			return null;
		}
		try {
			cursor = mDb.rawQuery("select * from " + tableName + " where id=?", new String[]{String.valueOf(id)});
		} catch(Exception e) {
			Log.e(TAG, "select", e);
		}
		cursor.moveToFirst();
		return cursor;
	}
	/**
	 * 删除表中指定记录
	 * @param tableName
	 * @param id
	 * @return
	 */
	public boolean delete(String tableName, String id) {
		boolean result = false;
		if (tableName == null || tableName.length() <= 0) {
			return false;
		}
		try {
			if (mDb.delete(tableName.trim(), "_id=?", new String[]{id}) > 0) {
				result = true;
			}
		} catch (Exception e) {
			result = false;
			Log.e(TAG, "delete", e);
		}
		return result;
	}
	/**
	 * 删除表中所有记录
	 * @param tableName
	 * @return
	 */
	public boolean deleteAll(String tableName) {
		boolean result = false;
		if (tableName == null || tableName.length() <= 0) {
			return false;
		}
		try {
			if (mDb.delete(tableName.trim(), null, null) > 0) {
				result = true;
			}
		} catch (Exception e) {
			result = false;
			Log.e(TAG, "deleteAll", e);
		}
		return result;
	}
	/**
	 * 查询表的记录数
	 * @param tableName
	 * @return
	 */
	public long getCount(String tableName) {
		long count = -1;
		if (tableName == null || tableName.length() <= 0) {
			return -1;
		}
		try {
			Cursor cursor = mDb.rawQuery("select count(*) from " + tableName.trim(), null);
			cursor.moveToFirst();
			count = cursor.getLong(0);
		} catch(Exception e) {
			Log.e(TAG, "getCount", e);
		}
		return count;
	}
 
	
	/**
	 * 创建表
	 * @param creatTableSQL
	 * @return
	 */
	public boolean creatTable(String creatTableSQL) {
		if(creatTableSQL == null || creatTableSQL.length() <= 0) {
			Log.e(TAG, "creatTable", new Throwable("the param is null or empty"));
			return false;
		}
		try {
			mDb.execSQL(creatTableSQL);
			return true;
		} catch(Exception e) {
			Log.e(TAG, "creatTable", e);
			return false;
		}
	}
	/**
	 * 删除表
	 * @param tableName
	 */
	public boolean dropTable(String tableName) {
		if (tableName == null || tableName.length() <= 0) {
			return false;
		}
		try {
			String sql = "drop table '" + tableName.trim() + "' ";
			mDb.execSQL(sql);
			return true;
		} catch (Exception e) {
			Log.e(TAG, "dropTable", e);
			return false;
		}
	}

	/**
	 * 判断表是否存在
	 * @param tableName
	 * @return
	 */
	public boolean tableIsExist(String tableName) {
		boolean result = false;
		if (tableName == null) {
			return false;
		}
		Cursor cursor = null;
		try {
			String sql = "select count(*) as c from Sqlite_master  where type ='table' and name ='"
					+ tableName.trim() + "'";
			cursor = mDb.rawQuery(sql, null);
			if (cursor.moveToNext()) {
				int count = cursor.getInt(0);
				if (count > 0) {
					result = true;
				}
			}
		} catch (Exception e) {
			result = false;
			Log.e(TAG, "tableIsExist", e);
		}
		return result;
	}
	
	/**
	 * 使用后务必关闭Cursor ！！！
	 * @param tableName
	 * @param offset
	 * @param maxResult
	 * @return Cursor
	 */
	public Cursor getScrollData(String tableName, int offset, int maxResult) {
		Cursor cursor = null;
		try {
			cursor = mDb.rawQuery("select * from " + tableName + " limit ?,?", 
					new String[]{String.valueOf(offset),String.valueOf(maxResult)});
		} catch(Exception e) {
			Log.e(TAG, "getScrollData", e);
		}
		return cursor;
	}
}
